﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class AccesoryDAL
    {

        #region[Accessory_SelectALL]
        public List<DTO.AccesoryDTO> Accessory_SelectALL()
        {
            List<DTO.AccesoryDTO> accall = new List<DTO.AccesoryDTO>();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectAllAccessory", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.AccesoryDTO acc = new DTO.AccesoryDTO();
                acc.AccessoryID     = dr.GetInt32(0);
                acc.Type            = dr.GetString(1);
                acc.Name            = dr.GetString(2);
                acc.Brand           = dr.GetString(3);
                acc.Color           = dr.GetString(4);
                acc.Prize           = dr.GetInt32(5);
                acc.WarrantyTime    = dr.GetInt32(6);
                acc.ImageID         = dr.GetInt32(7);
                acc.Count           = dr.GetInt32(8);
                accall.Add(acc);
            }
            dr.Close();
            con.Close();

            return accall;
        }
        #endregion

        #region[Accessory_SelectByName]
        public List<DTO.AccesoryDTO> Accessory_SelectByName(String name)
        {
            List<DTO.AccesoryDTO> accall = new List<DTO.AccesoryDTO>();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectAccessoryByName", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@name", name);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.AccesoryDTO acc = new DTO.AccesoryDTO();
                acc.AccessoryID = dr.GetInt32(0);
                acc.Type = dr.GetString(1);
                acc.Name = dr.GetString(2);
                acc.Brand = dr.GetString(3);
                acc.Color = dr.GetString(4);
                acc.Prize = dr.GetInt32(5);
                acc.WarrantyTime = dr.GetInt32(6);
                acc.ImageID = dr.GetInt32(7);
                acc.Count = dr.GetInt32(8);
                accall.Add(acc);
            }
            dr.Close();
            con.Close();

            return accall;
        }
        #endregion

        #region[Accessory_SelectByID]
        public DTO.AccesoryDTO Accessory_SelectByID(int id)
        {
            DTO.AccesoryDTO acc = new DTO.AccesoryDTO();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectAccessoryByID", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@accessoryid", id);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                acc.AccessoryID = dr.GetInt32(0);
                acc.Type = dr.GetString(1);
                acc.Name = dr.GetString(2);
                acc.Brand = dr.GetString(3);
                acc.Color = dr.GetString(4);
                acc.Prize = dr.GetInt32(5);
                acc.WarrantyTime = dr.GetInt32(6);
                acc.ImageID = dr.GetInt32(7);
                acc.Count = dr.GetInt32(8);
            }
            dr.Close();
            con.Close();
            return acc;
        }
        #endregion

        #region[Accessory_SelectByType]
        public List<DTO.AccesoryDTO> Accessory_SelectByType(string type)
        {
            List<DTO.AccesoryDTO> acctype = new List<DTO.AccesoryDTO>();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectAccessoryByType", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@type", type);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.AccesoryDTO acc = new DTO.AccesoryDTO();
                acc.AccessoryID = dr.GetInt32(0);
                acc.Type = dr.GetString(1);
                acc.Name = dr.GetString(2);
                acc.Brand = dr.GetString(3);
                acc.Color = dr.GetString(4);
                acc.Prize = dr.GetInt32(5);
                acc.WarrantyTime = dr.GetInt32(6);
                acc.ImageID = dr.GetInt32(7);
                acc.Count = dr.GetInt32(8);
                acctype.Add(acc);
            }
            dr.Close();
            con.Close();
            return acctype;
        }
        #endregion

        #region [Accessory_Insert]
        public bool Accessory_Insert(DTO.AccesoryDTO acc)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("AddAccessory", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@type", acc.Type));
            cmd.Parameters.Add(new MySqlParameter("@name", acc.Name));
            cmd.Parameters.Add(new MySqlParameter("@brand", acc.Brand));
            cmd.Parameters.Add(new MySqlParameter("@color", acc.Color));
            cmd.Parameters.Add(new MySqlParameter("@prize", acc.Prize));
            cmd.Parameters.Add(new MySqlParameter("@wartime", acc.WarrantyTime));
            cmd.Parameters.Add(new MySqlParameter("@imageid", acc.ImageID));
            cmd.Parameters.Add(new MySqlParameter("@count", acc.Count));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

        #region [Accessory_Update]
        public bool Accessory_Update(DTO.AccesoryDTO acc)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("UpdateAccessory", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(new MySqlParameter("@accessoryid", acc.AccessoryID));
            cmd.Parameters.Add(new MySqlParameter("@type", acc.Type));
            cmd.Parameters.Add(new MySqlParameter("@name", acc.Name));
            cmd.Parameters.Add(new MySqlParameter("@brand", acc.Brand));
            cmd.Parameters.Add(new MySqlParameter("@color", acc.Color));
            cmd.Parameters.Add(new MySqlParameter("@prize", acc.Prize));
            cmd.Parameters.Add(new MySqlParameter("@warrantytime", acc.WarrantyTime));
            cmd.Parameters.Add(new MySqlParameter("@imageid", acc.ImageID));
            cmd.Parameters.Add(new MySqlParameter("@count", acc.Count));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

        #region [Accessory_Delete]
        public bool Accessory_DeleteByID(DTO.AccesoryDTO acc)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("DeleteAccessoryByID", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@accessoryid", acc.AccessoryID));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

    }
}
